package jsu.ljy.lostandfind.dao.impl;

import jsu.ljy.lostandfind.dao.BaseDao;
import jsu.ljy.lostandfind.model.Item;
import jsu.ljy.lostandfind.util.DBUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 物品数据访问实现类
 * 
 * <p>实现了物品相关的数据库操作，包括物品信息的增删改查等基本操作。
 * 提供了物品的发布、更新、删除、查询等功能的数据库访问实现。</p>
 * 
 * @author 罗嘉毅
 * @version 1.0
 * @since 2024-03-21
 */
@Repository
public class ItemDaoImpl implements BaseDao<Item> {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 保存物品信息
     * 
     * @param item 物品信息
     * @throws RuntimeException 当数据库操作失败时抛出
     */
    @Override
    public void save(Item item) {
        // 生成ID
        if (item.getId() == null) {
            item.setId(generateId());
        }
        
        // 执行保存操作
        String sql = "INSERT INTO items (id, name, description, image, location, create_time, status, user_id) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        try {
            jdbcTemplate.update(sql,
                item.getId(),
                item.getName(),
                item.getDescription(),
                item.getImage(),
                item.getLocation(),
                item.getCreateTime(),
                item.getStatus(),
                item.getUserId()
            );
        } catch (Exception e) {
            throw new RuntimeException("保存物品信息失败", e);
        }
    }
    
    private Long generateId() {
        String sql = "SELECT MAX(id) FROM items";
        Long maxId = jdbcTemplate.queryForObject(sql, Long.class);
        return (maxId == null ? 0 : maxId) + 1;
    }
    
    /**
     * 更新物品信息
     * 
     * @param item 物品信息
     * @throws RuntimeException 当数据库操作失败时抛出
     */
    @Override
    public void update(Item item) {
        String sql = "UPDATE items SET name=?, description=?, image=?, location=?, status=? WHERE id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, item.getName());
            pstmt.setString(2, item.getDescription());
            pstmt.setString(3, item.getImage());
            pstmt.setString(4, item.getLocation());
            pstmt.setString(5, item.getStatus());
            pstmt.setLong(6, item.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("更新失物招领信息失败", e);
        }
    }

    /**
     * 删除物品及其相关信息
     * 
     * <p>删除物品时会同时删除与之相关的认领记录和评论。</p>
     * 
     * @param id 物品ID
     * @throws RuntimeException 当数据库操作失败时抛出
     */
    @Override
    public void delete(Long id) {
        // 先删除认领记录
        String deleteClaimsSQL = "DELETE FROM claim_info WHERE item_id = ?";
        // 再删除评论
        String deleteCommentsSQL = "DELETE FROM comments WHERE item_id = ?";
        // 最后删除物品
        String deleteItemSQL = "DELETE FROM items WHERE id = ?";
        
        try (Connection conn = DBUtil.getConnection()) {
            // 开启事务
            conn.setAutoCommit(false);
            try {
                // 删除认领记录
                try (PreparedStatement pstmt = conn.prepareStatement(deleteClaimsSQL)) {
                    pstmt.setLong(1, id);
                    pstmt.executeUpdate();
                }
                
                // 删除评论
                try (PreparedStatement pstmt = conn.prepareStatement(deleteCommentsSQL)) {
                    pstmt.setLong(1, id);
                    pstmt.executeUpdate();
                }
                
                // 删除物品
                try (PreparedStatement pstmt = conn.prepareStatement(deleteItemSQL)) {
                    pstmt.setLong(1, id);
                    pstmt.executeUpdate();
                }
                
                // 提交事务
                conn.commit();
            } catch (SQLException e) {
                // 发生错误时回滚事务
                conn.rollback();
                throw e;
            } finally {
                // 恢复自动提交
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            throw new RuntimeException("删除物品失败", e);
        }
    }

    /**
     * 根据ID查询物品
     * 
     * @param id 物品ID
     * @return 物品信息，不存在时返回null
     * @throws RuntimeException 当数据库操作失败时抛出
     */
    @Override
    public Item findById(Long id) {
        String sql = "SELECT * FROM items WHERE id=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return mapResultSetToItem(rs);
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询失物招领信息失败", e);
        }
        return null;
    }

    /**
     * 查询所有物品
     * 
     * @return 物品列表，按创建时间降序排序
     * @throws RuntimeException 当数据库操作失败时抛出
     */
    @Override
    public List<Item> findAll() {
        String sql = "SELECT * FROM items ORDER BY create_time DESC";
        List<Item> items = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                items.add(mapResultSetToItem(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询失物招领信息列表失败", e);
        }
        return items;
    }

    /**
     * 将ResultSet映射为Item对象
     * 
     * @param rs 结果集
     * @return Item对象
     * @throws SQLException 当数据库操作失败时抛出
     */
    private Item mapResultSetToItem(ResultSet rs) throws SQLException {
        Item item = new Item();
        item.setId(rs.getLong("id"));
        item.setName(rs.getString("name"));
        item.setDescription(rs.getString("description"));
        item.setImage(rs.getString("image"));
        item.setLocation(rs.getString("location"));
        item.setCreateTime(rs.getTimestamp("create_time"));
        item.setStatus(rs.getString("status"));
        item.setUserId(rs.getLong("user_id"));
        return item;
    }

    // ... 实现其他接口方法
} 